use crate::model::{ColumnInfo, ForeignKeyInfo, TableInfo};

use sqlparser::ast::{ColumnOption, CreateTable};
use sqlparser::ast::{Statement, TableConstraint};
use sqlparser::dialect::{
    AnsiDialect, BigQueryDialect, ClickHouseDialect, DatabricksDialect, Dialect, DuckDbDialect,
    GenericDialect, HiveDialect, MsSqlDialect, MySqlDialect, PostgreSqlDialect, RedshiftSqlDialect,
    SQLiteDialect, SnowflakeDialect,
};
use sqlparser::parser::Parser;

fn get_dialect(dialect: &str) -> &'static dyn Dialect {
    match dialect.to_lowercase().as_str() {
        "ansi" => &AnsiDialect {},
        "bigquery" => &BigQueryDialect {},
        "clickhouse" => &ClickHouseDialect {},
        "databricks" => &DatabricksDialect {},
        "duckdb" => &DuckDbDialect {},
        "generic" | "" => &GenericDialect {},
        "hive" => &HiveDialect {},
        "mysql" => &MySqlDialect {},
        "mssql" => &MsSqlDialect {},
        "postgresql" => &PostgreSqlDialect {},
        "redshift" => &RedshiftSqlDialect {},
        "snowflake" => &SnowflakeDialect {},
        "sqlite" | "sqlite3" => &SQLiteDialect {},
        _ => &GenericDialect {}, // 默认返回 GenericDialect
    }
}

pub fn parse_sql(sql: String, dialect: &str) -> Result<Vec<TableInfo>, String> {
    let processed_sql = preprocess_sql(sql);
    let dialect = get_dialect(dialect);
    let ast =
        Parser::parse_sql(dialect, &processed_sql).map_err(|e| format!("SQL解析错误: {}", e))?;

    let mut tables = Vec::new();
    for stmt in ast {
        if let Statement::CreateTable(create_table) = stmt {
            let table_info = parse_create_table(create_table);
            tables.push(table_info);
        }
    }
    Ok(tables)
}

fn parse_create_table(ct: CreateTable) -> TableInfo {
    let table_name = ct.name.to_string();
    let mut table_info = TableInfo::new(table_name);

    match ct.comment {
        Some(comm) => match comm {
            sqlparser::ast::CommentDef::WithEq(comm) => table_info.comment = comm.to_string(),
            sqlparser::ast::CommentDef::WithoutEq(comm) => table_info.comment = comm.to_string(),
            sqlparser::ast::CommentDef::AfterColumnDefsWithoutEq(comm) => {
                table_info.comment = comm.to_string()
            }
        },
        None => table_info.comment = "".to_owned(),
    }

    let mut cols: Vec<ColumnInfo> = Vec::new();
    let mut pmks: Vec<String> = Vec::new();
    let mut fks: Vec<ForeignKeyInfo> = Vec::new();

    for col in &ct.columns {
        let mut column_info = ColumnInfo {
            name: col.name.to_string(),
            r#type: col.data_type.to_string(),
            nullable: true,
            default_value: String::new(),
            comment: String::new(),
        };

        // 处理列约束和注释
        for option in &col.options {
            match &option.option {
                ColumnOption::NotNull => column_info.nullable = false,
                ColumnOption::Comment(comment) => {
                    column_info.comment = comment.trim_matches('\'').to_string();
                }
                ColumnOption::Default(expr) => {
                    column_info.default_value = expr.to_string();
                }
                _ => {}
            }
        }
        cols.push(column_info);
    }

    // 解析约束（主键和外键）
    for constraint in &ct.constraints {
        match constraint {
            TableConstraint::PrimaryKey { columns, .. } => {
                for col in columns {
                    pmks.push(col.to_string());
                }
            }
            TableConstraint::ForeignKey {
                columns,
                referred_columns,
                foreign_table,
                ..
            } => {
                fks.push(ForeignKeyInfo {
                    column_name: columns.iter().map(|c| c.to_string()).collect(),
                    reference_table: foreign_table.to_string(),
                    reference_column: referred_columns.iter().map(|c| c.to_string()).collect(),
                });
            }
            _ => {}
        }
    }

    table_info.primary_keys = pmks;
    table_info.foreign_keys = fks;
    table_info.columns = cols;

    table_info
}

fn preprocess_sql(sql: String) -> String {
    sql.lines()
        .filter(|line| !line.trim().to_uppercase().contains("UNIQUE INDEX"))
        .collect::<Vec<&str>>()
        .join("\n")
}
